import { Logger } from './log.js';
const fs = require('fs');

const sqlite3 = require('sqlite3').verbose();

let logger = new Logger();

export class SQLiteManager {
  constructor(sqlPath, log) {
    this.dbPath = sqlPath;
    logger = log;

    if (!fs.existsSync(sqlPath)) {
      // 创建数据库文件
      new sqlite3.Database(sqlPath, sqlite3.OPEN_READWRITE | sqlite3.OPEN_CREATE, (err) => {
        if (err) {
          logger.error(err.message);
        }
        logger.info('创建数据库文件,path:' + sqlPath);
      });
    }

    this.initCmdTable()
  }


  // 私有方法，用于创建数据库连接
  _createConnection() {
    return new Promise((resolve, reject) => {
      const db = new sqlite3.Database(this.dbPath, (err) => {
        if (err) {
          reject(err);
        } else {
          resolve(db);
        }
      });
    });
  }


  // 查询数据
  query(sql, params = []) {
    return this._createConnection().then(db => {
      return new Promise((resolve, reject) => {
        db.all(sql, params, (err, rows) => {
          db.close(); // 查询完成后关闭连接
          if (err) {
            reject(err);
          } else {
            logger.info('Query executed successfully.');
            resolve(rows);
          }
        });
      });
    });
  }

  // 插入数据
  insert(sql, params = []) {
    return this._createConnection().then(db => {
      return new Promise((resolve, reject) => {
        db.run(sql, params, function (err) {
          db.close(); // 插入完成后关闭连接
          if (err) {
            reject(err);
          } else {
            logger.info('Insert executed successfully. sql: ' + sql + ' Row inserted with rowid: ' + this.lastID);
            resolve(this.lastID);
          }
        });
      });
    });
  }

  delete(sql, params = []) {
    return this._createConnection().then(db => {
      return new Promise((resolve, reject) => {
        db.run(sql, params, function (err) {
          db.close(); // 插入完成后关闭连接
          if (err) {
            reject(err);
          } else {
            logger.info('Insert executed successfully. Row inserted with rowid: ' + this.lastID);
            resolve(this.lastID);
          }
        });
      });
    });
  }

  // 更新数据
  update(sql, params = []) {
    return this._createConnection().then(db => {
      return new Promise((resolve, reject) => {
        db.run(sql, params, function (err) {
          db.close(); // 更新完成后关闭连接
          if (err) {
            reject(err);
          } else {
            logger.info('Update executed successfully. Rows updated: ' + this.changes);
            resolve(this.changes);
          }
        });
      });
    });
  }

  // 插入数据
  create_table(sql, params = []) {
    return this._createConnection().then(db => {
      return new Promise((resolve, reject) => {
        db.run(sql, params, function (err) {
          db.close(); // 插入完成后关闭连接
          if (err) {
            reject(err);
          } else {
            logger.info('create_table executed successfully. Row inserted with rowid: ' + this.lastID);
            resolve(this.lastID);
          }
        });
      });
    });
  }

  initCmdTable() {
    (
      //创建cmd表
      async () => {
        let cmd_table = 'CREATE TABLE IF NOT EXISTS cmd_table (id INTEGER PRIMARY KEY AUTOINCREMENT, cmd TEXT, param TEXT, create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP)';
        await this.create_table(cmd_table)

        let exec_info_table = 'CREATE TABLE IF NOT EXISTS exec_info_table ( id INTEGER PRIMARY KEY, job_command TEXT, execution_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,execution_result TEXT )'
        await this.create_table(exec_info_table)

      }
      //创建其他表
    )();

  }


}

// module.exports = SQLiteManager;